#!/usr/local/bin/python evn
# -*- coding: utf-8 -*-

'''
   数据库连接
'''

import logging
import traceback
import MySQLdb
import MySQLdb.cursors
from DBUtils.PooledDB import PooledDB
import const
import util
logger = logging.getLogger(__name__)

#连接串
DBCONN = {}
DBCONN["host"] = const.Config_Host                  #DB服务器
DBCONN["port"] = const.Config_DB_Port               #DB服务器端口
DBCONN["user"] = const.Config_User                  #DB用户名
DBCONN["passwd"] = const.Config_Pwd                 #DB密码
DBCONN["db"] = const.Config_DB                      #DB数据库
DBCONN["cursorclass"] = MySQLdb.cursors.DictCursor

#DBCONN = dict(host='192.168.1.129', user='root', passwd='', db='love_island',cursorclass=MySQLdb.cursors.DictCursor)
#DBCONN = dict(host='192.168.1.233', user='root', passwd='root', db='love_island',cursorclass=MySQLdb.cursors.DictCursor)

logger = logging.getLogger(__name__)

class mysql():      
    db_pooled = PooledDB(MySQLdb, **DBCONN)
    
    def __init__(self,DBCONN=DBCONN):       
        #self.pool = PooledDB(MySQLdb,1,10,0,0,0,0,**DBCONN)
        self.conn = mysql.db_pooled.connection()
        self.cur = self.conn.cursor()
        self.query("SET NAMES utf8")
        
    def getRow(self,sql):      
        self.query(sql)
        row = self.cur.fetchone()
        return row
    
    def escape_string(self, strings):
        return mysql.escape_stringExt(strings)
    
    @staticmethod
    def escape_stringExt(strVal): 
        if isinstance(strVal, unicode):
            strVal = strVal.encode('utf-8')        
        return MySQLdb.escape_string(strVal)
        
    def getAll(self,sql):
        self.query(sql)
        all = self.cur.fetchall()
        return all
    
    def query(self,sql):
        try:
            if isinstance(sql, unicode):
                sql = sql.encode('utf-8')
            print sql
            
            res = self.cur.execute(sql)
            self.conn.commit()
            return res
        except Exception:
            logger.warning('mysql, query error:\n%s' % traceback.format_exc())
            return None
       
    @staticmethod
    def createInserStrSql(table, data):
        cmd = comma=''
        if type(data)==dict:  
            for k,v in data.iteritems(): 
                if type(v)==str:
                    v = mysql.escape_stringExt(v)                   
                cmd=cmd+comma+" `%s`='%s' " %(k,v)
                comma=','   
            sql = "INSERT INTO %s SET %s " %(table,cmd)
        else:#str
            sql = "INSERT INTO %s SET %s " %(table,data)
        
        return sql
        
    def inData(self,table,data,insert_id=None):
        sql = mysql.createInserStrSql(table, data)
        
        if insert_id:
            self.query(sql) 
            last_id = self.cur.lastrowid           
            self.conn.commit()           
            return last_id
        else:
            return self.query(sql)    
   
    def upData(self,table,data,where,add=None,asyn=None,asynCallback=None): 
        cmd = comma=''
        if type(data)==dict:  
            for k,v in data.iteritems():
                if type(v)==str:
                    v = self.escape_string(v)
                if add:
                    cmd+=comma+" `%s`=`%s`+%d " %(k,k,v)
                else:    
                    cmd+=comma+" `%s`='%s' " %(k,v)
                comma=',' 
            sql = "UPDATE %s SET %s %s " %(table,cmd,where)
        else:
            sql = "UPDATE %s SET %s %s " %(table,data,where)
        if asyn:
            util.asynOpenUrl(const.ConfigAsynDbUrl, 'POST', {'sql':sql}, callback=asynCallback)
        else:
            return self.query(sql)
    
    def __del__(self):
        #self.conn=None
        self.cur=None
    
#sql = "select * from t_user"        
#conn = mysql()
#row = conn.getRow(sql)
#print row

